package com.authine.cloudpivot.web.api.secondopen.mapper;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;
import java.util.Map;

/**
 *
 */
public class NewsSqlProvider {
    /**
     * 查询记录数量
     *
     * @param para
     * @return
     */
    public String Count(Map para) {
        String string = new SQL() {
            {
                SELECT("count(id)");
                FROM(para.get("tableName").toString());
                if (para.get("recordType") != null) WHERE("recordType=#{recordType}");
                if (para.get("bizObjectId") != null) WHERE("bizObjectId=#{bizObjectId}");
                if (para.get("creater") != null) WHERE("creater=#{creater}");
            }
        }.toString();
        return string;
    }


    /**
     * 查询分类已读记录数量
     *
     * @param para
     * @return
     */
    public String ReadCount(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT count(a.id) FROM ");
        sql.append(para.get("tableName"));
        sql.append(" a INNER JOIN h_biz_attachment b ON  a.bizObjectId = b.id ");
        sql.append("WHERE a.recordType='R' ");
        sql.append("AND creater=#{creater} ");
        return sql.toString();
    }

    /**
     * 查询文库目录
     *
     * @param para
     * @return
     */
    public String selectLibraryTypeList(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT id, libraryName FROM ");
        sql.append(para.get("tableName"));
        sql.append(" WHERE status='启用' ");
        if (para.get("catalog") != null && StringUtils.isNotBlank(para.get("catalog").toString())) {
            sql.append("AND libraryType = #{catalog}");
        }
        sql.append(" ORDER BY sort ASC");
        return sql.toString();
    }

    /**
     * 查询新闻条数
     *
     * @param para
     * @return
     */
    public String selectNewsListCount(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(id) FROM ");
        sql.append(para.get("tableName"));
        if(para.get("newsType")==null){
            sql.append(" WHERE newsStatus = '正常' AND (newsType='集团新闻' or newsType='行政新闻' )");
        }else if("全站搜索文库".equals(para.get("newsType"))){
            sql.append(" WHERE newsStatus = '正常'  ");
        }else{
            sql.append(" WHERE newsStatus = '正常' AND newsType=#{newsType} ");
        }

        sql.append("AND (IfShow='1'");
        List<String> orgs = (List<String>) para.get("permission");
        for (String org : orgs) {
            sql.append(" OR permission LIKE '%");
            sql.append(org);
            sql.append("%'");
        }
        sql.append(")");
        return sql.toString();
    }

    /**
     * 查询新闻列表
     *
     * @param para
     * @return
     */
    public String selectNewsList(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT id, IfShow,author, title,content,publishTime,top,creater,newsType FROM ");
        sql.append(para.get("tableName"));
        if(para.get("newsType")==null){
            sql.append(" WHERE newsStatus = '正常' AND (newsType='集团新闻' or newsType='行政新闻' )");
        }else if("全站搜索文库".equals(para.get("newsType"))){
            sql.append(" WHERE newsStatus = '正常'  ");
        }else{
            sql.append(" WHERE newsStatus = '正常' AND newsType=#{newsType} ");
        }
        if (para.get("title") != null && StringUtils.isNotBlank(para.get("title").toString())) {
            sql.append("AND (title LIKE CONCAT('%',#{title},'%') )");
        }
        sql.append("AND (IfShow='1' or IfShow='0'");
        List<String> orgs = (List<String>) para.get("permission");
        for (String org : orgs) {
            sql.append(" OR permission LIKE '%");
            sql.append(org);
            sql.append("%'");
        }
        sql.append(")");
        return sql.toString();
    }



    /**
     * 查询新闻列表
     *
     * @param para
     * @return
     */
    public String selectIndexNewsList(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT id, IfShow,author, title,content,publishTime,top,creater,newsType FROM ");
        sql.append(para.get("tableName"));
        if(para.get("newsType")==null){
            sql.append(" WHERE newsStatus = '正常' AND (newsType='集团新闻' or newsType='行政新闻' )");
        }else if("全站搜索文库".equals(para.get("newsType"))){
            sql.append(" WHERE newsStatus = '正常'  ");
        }else{
            sql.append(" WHERE newsStatus = '正常' AND newsType=#{newsType} ");
        }
        if (para.get("title") != null && StringUtils.isNotBlank(para.get("title").toString())) {
            sql.append("AND (title LIKE CONCAT('%',#{title},'%') )");
        }
        return sql.toString();
    }


    public String selectNewsList2(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT a.id, a.IfShow,a.author, a.title,a.content,a.publishTime,a.top,a.creater,a.newsType,b.name FROM ");
        sql.append(para.get("tableName"));
        if (para.get("newsType") == null) {
//            sql.append(" a left join h_org_user b on substring_index(substring_index(a.author,'[{\"id\":\"',-1),'\",\"type\":3}]',1) =b.id WHERE a.newsStatus = '正常' AND (a.newsType='集团新闻' or a.newsType='行政新闻' )");
            sql.append(" a left join h_org_user b on substring_index(substring_index(a.author,'[{\"id\":\"',-1),'\",\"type\":3}]',1) =b.id WHERE a.newsStatus = '正常' ");
        } else if ("全站搜索文库".equals(para.get("newsType"))) {
            sql.append(" WHERE a.newsStatus = '正常'  ");
        } else {
            sql.append(" a left join h_org_user b on substring_index(substring_index(a.author,'[{\"id\":\"',-1),'\",\"type\":3}]',1) =b.id  WHERE a.newsStatus = '正常' AND a.newsType=#{newsType} ");
        }
        // 是否筛选历史重要通知
        if (para.get("isImportant") != null) {
            sql.append(" AND a.isImportant = '1' ");
        }
        // 是否按照时间筛选
        if (para.get("fromTime") != null && para.get("toTime") != null) {
            sql.append("AND (a.createdTime between #{fromTime} and #{toTime})");
        }
        if (para.get("title") != null && StringUtils.isNotBlank(para.get("title").toString())) {
            sql.append("AND (a.title LIKE CONCAT('%',#{title},'%') or a.keyword LIKE CONCAT('%',#{title},'%') or b.name LIKE CONCAT('%',#{title},'%'))");
        }
        sql.append("AND (a.IfShow='1'");
        List<String> orgs = (List<String>) para.get("permission");
        for (String org : orgs) {
            sql.append(" OR a.permission LIKE '%");
            sql.append(org);
            sql.append("%'");
        }
        sql.append(")");
        return sql.toString();
    }

    /**
     * 查询新闻列表
     *
     * @param para
     * @return
     */
    public String selectNewsListByDept(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT id, IfShow,author, title,content,publishTime,top,creater,newsType FROM ");
        sql.append(para.get("tableName"));
        sql.append(" WHERE newsStatus = '正常'  ");
        if (para.get("title") != null && StringUtils.isNotBlank(para.get("title").toString())) {
            sql.append("AND title LIKE CONCAT('%',#{title},'%') ");
        }
        sql.append("AND (IfShow='1'");
        List<String> orgs = (List<String>) para.get("permission");
        for (String org : orgs) {
            sql.append(" or permission LIKE '%");
            sql.append(org);
            sql.append("%'");
        }

        sql.append(")");
        return sql.toString();
    }

    /**
     * 查询图片新闻
     *
     * @param para
     * @return
     */
    public String selectImageNewsList(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT a.id, a.IfShow, a.title,a.content,a.publishTime,a.top,a.creater,a.newsType FROM ");
        sql.append(para.get("tableName"));
        sql.append(" a INNER JOIN h_biz_attachment b ON a.id = b.bizObjectId ");
        sql.append("WHERE b.bizPropertyCode= 'pictrue' ");
        sql.append("AND a.newsStatus = '正常' AND a.newsType=#{newsType} ");
        sql.append("AND (a.IfShow='1'");
        List<String> orgs = (List<String>) para.get("permission");
        for (String org : orgs) {
            sql.append(" OR a.permission LIKE '%");
            sql.append(org);
            sql.append("%'");
        }
        sql.append(")");
        return sql.toString();
    }

    public String selectTop(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT id FROM ");
        sql.append(para.get("tableName") );
        sql.append(" WHERE publishTime " + para.get("flag") +" #{publishTime} ");
        sql.append("AND newsStatus = '正常' AND newsType=#{newsType} AND top = #{top} ");
        sql.append("AND (IfShow='1'");
        List<String> orgs = (List<String>) para.get("permission");
        for (String org : orgs) {
            sql.append(" OR permission LIKE '%");
            sql.append(org);
            sql.append("%'");
        }
        sql.append(") ");
        sql.append("ORDER BY top " + para.get("order") +" ,publishTime " + para.get("order") +" LIMIT 1");
        return sql.toString();
    }

    public String selectTopMin(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT id FROM ");
        sql.append(para.get("tableName"));
        sql.append(" WHERE  newsStatus = '正常' AND newsType=#{newsType} AND top = #{top} ");
        sql.append("AND (IfShow='1'");
        List<String> orgs = (List<String>) para.get("permission");
        for (String org : orgs) {
            sql.append(" OR permission LIKE '%");
            sql.append(org);
            sql.append("%'");
        }
        sql.append(") ");
        sql.append("ORDER BY top " + para.get("order") +" ,publishTime " + para.get("order") +" LIMIT 1");
        return sql.toString();
    }

    public String selectNomal(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT id FROM ");
        sql.append(para.get("tableName"));
        sql.append(" WHERE publishTime " + para.get("flag") +" #{publishTime} ");
        sql.append("AND newsStatus = '正常' AND newsType=#{newsType} AND top = #{top}");
        sql.append("AND (IfShow='1'");
        List<String> orgs = (List<String>) para.get("permission");
        for (String org : orgs) {
            sql.append(" OR permission LIKE '%");
            sql.append(org);
            sql.append("%'");
        }
        sql.append(") ");
        sql.append("ORDER BY top " + para.get("order") +" ,publishTime " + para.get("order") +" LIMIT 1");
        return sql.toString();
    }

    public String selectNomalMax(Map para) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT id FROM ");
        sql.append(para.get("tableName"));
        sql.append(" WHERE  newsStatus = '正常' AND newsType=#{newsType} AND top = #{top} ");
        sql.append("AND (IfShow='1'");
        List<String> orgs = (List<String>) para.get("permission");
        for (String org : orgs) {
            sql.append(" OR permission LIKE '%");
            sql.append(org);
            sql.append("%'");
        }
        sql.append(") ");
        sql.append("ORDER BY top "+ para.get("order"));
        sql.append(",publishTime "+ para.get("order") +" limit 1");
        return sql.toString();
    }
}
